package callback;

public class TestSql {
	private static String sql = "select v.DISTRICT_CODE," + " v.DISTRICT_NAME,"
			+ " sum(LAND_AREA) as LAND_AREA,"
			+ " sum(PLOUGH_AREA) as PLOUGH_AREA,"
			+ " sum(LOW_TOTAL_AREA) as LOW_TOTAL_AREA,"
			+ " sum(GOV_AREA) as GOV_AREA," + " sum(UNGOV_AREA) as UNGOV_AREA"
			+ " from total_area t, district v, state s"
			+ " where t.district_id = v.district_id   "
			+ " and v.district_steps = 1" + " and t.state_id = s.state_id";
	private static String sql1 = "select v.DISTRICT_CODE," + "v.DISTRICT_NAME,"
			+ " sum(LAND_AREA) as LAND_AREA1,"
			+ " sum(PLOUGH_AREA) as PLOUGH_AREA1,"
			+ " sum(LOW_TOTAL_AREA) as LOW_TOTAL_AREA1,"
			+ " sum(GOV_AREA) as GOV_AREA1," + "sum(UNGOV_AREA) as UNGOV_AREA1"
			+ " from saline_total_area t, district v, state s"
			+ " where t.district_id = v.district_id    "
			+ " and v.district_steps = 1" + " and t.state_id = s.state_id";
	private static String sql2 = "select  nvl(t1.DISTRICT_CODE,t2.DISTRICT_CODE)as DISTRICT_CODE,"
			+ " nvl(t1.DISTRICT_NAME,t2.DISTRICT_NAME)as DISTRICT_NAME,"
			+ " t1.LAND_AREA,t1.PLOUGH_AREA,t1.LOW_TOTAL_AREA,t1.GOV_AREA,t1.UNGOV_AREA,"
			+ " t2.LAND_AREA1,t2.PLOUGH_AREA1,t2.LOW_TOTAL_AREA1,t2.GOV_AREA1,t2.UNGOV_AREA1 "
			+ " from (";

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		StringBuffer sb = new StringBuffer();
		sb.append(sql2);
		sb.append(sql);
		sb.append("  and t.apply_year = 2009 ");
		sb.append("  and t.state_id in (1, 4, 5)");
		sb.append("	 group by v.DISTRICT_NAME, v.DISTRICT_CODE) t1");
		sb.append("	 left join (");
		sb.append(sql1);
		sb.append("  and t.apply_year = 2009 ");
		sb.append("  and t.state_id in (1, 4, 5)");
		sb.append("	 group by v.DISTRICT_NAME, v.DISTRICT_CODE) t2 on t1.DISTRICT_CODE = t2.DISTRICT_CODE");
		sb.append(" union ");
		sb.append(sql2);
		sb.append(sql);
		sb.append("  and t.apply_year = 2009 ");
		sb.append("  and t.state_id in (1, 4, 5)");
		sb.append("	 group by v.DISTRICT_NAME, v.DISTRICT_CODE) t1");
		sb.append("	 right join (");
		sb.append(sql1);
		sb.append("  and t.apply_year = 2009 ");
		sb.append("  and t.state_id in (1, 4, 5)");
		sb.append("	 group by v.DISTRICT_NAME, v.DISTRICT_CODE) t2 on t1.DISTRICT_CODE = t2.DISTRICT_CODE");
		
		System.out.println(sb);

	}

}
